package com.rybbaby.tss.front.tssMemberPackage.dao.imp;

import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssMemberCheckAttendance.entity.TssMemberCheckAttendance;
import com.rybbaby.tss.front.tssMemberPackage.dao.ITssMemberPackageDao;
import com.rybbaby.tss.front.tssMemberPackage.entity.TssMemberPackage;
import com.rybbaby.tss.front.tssMemberPackageHistory.entity.TssMemberPackageHistory;
import com.rybbaby.tss.front.tssStatistics.entity.ViewParentingSurplusClassHour;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author 雷春琼
 * @description 会员课时包数据库操作实现
 * @createDate 2017-08-10 上午12:04:35
 */
@Repository
public class TssMemberPackageDaoImp extends GenericDao<TssMemberPackage> implements ITssMemberPackageDao {

    /**
     * 根据会员id查询购买课时包信息
     *
     * @param memberId
     * @return
     */
    @Override
    public List<Map<String, Object>> getListByMemberId(String memberId, Integer type) throws Exception {
        String sql = "SELECT\n" +
                "\tm.*,\n" +
                "\tp.`name`,\n" +
                "\tp.id packageId,\n" +
                "\tp.totalClassHour,\n" +
                "\tp.originalUnitPrice,\n" +
                "\te.userName\n" +
                "FROM\n" +
                "\ttss_member_package m\n" +
                "LEFT JOIN tss_student s ON m.memberId = s.id\n" +
                "LEFT JOIN tss_package p ON p.id = m.packageId\n" +
                "LEFT JOIN sys_admin_user e ON e.id = m.adminUserId\n" +
                "WHERE\n" +
                "\tm.memberId = ?\n";
        if (type != null) {
            sql += "AND m.type = ?";
            return this.findForJdbc(sql, new Object[]{memberId, type});
        } else {
            return this.findForJdbc(sql, new Object[]{memberId});
        }
    }

    /**
     * 修改会员课时包状态
     *
     * @param packageStatus
     * @return
     */
    @Override
    public Integer updatePackageStatus(String targetDateId, Integer packageStatus) {
        String sql = "update tss_member_package set updateTime=Now(),packageStatus=? where id=?";
        return this.executeSql(sql, new Object[]{packageStatus, targetDateId});
    }

    @Override
    public Integer updateParameterById(Map<String, Object> map, String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("update tss_member_package set  ");
        if (map.size() > 0) {
            for (Map.Entry<String, Object> vo : map.entrySet()) {
                sql.append(vo.getKey() + "=" + "'" + vo.getValue() + "',");
            }
            sql.deleteCharAt(sql.length() - 1);
        }
        sql.append(" where id='" + id + "'");
        return this.updateBySql(sql.toString());
    }

    @Override
    public int deductiveHours(Map<String, Object> packageInformation) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " consumptionClassHour = consumptionClassHour + " + packageInformation.get("formalClass") + "+" + packageInformation.get("giftClass") + ", \n" +
                " positiveConsumptionClassHour = positiveConsumptionClassHour + " + packageInformation.get("formalClass") + ",\n" +
                " giftConsumptionClassHour = giftConsumptionClassHour + " + packageInformation.get("giftClass") + ",\n" +
                " unusedClassHour = unusedClassHour - " + packageInformation.get("formalClass") + "-" + packageInformation.get("giftClass") + ",\n" +
                " positiveUnusedClassHour = positiveUnusedClassHour - " + packageInformation.get("formalClass") + ",\n" +
                " giftUnusedClassHour = giftUnusedClassHour - " + packageInformation.get("giftClass") + ",\n" +
                " updateTime = now()" +
                "WHERE\n" +
                "\ttss_member_package.id = '" + packageInformation.get("memberPackageId") + "'";
        System.out.println("sql:" + sql);
        return this.updateBySql(sql);
    }

    @Override
    public int deductiveAttendanceDay(Map<String, Object> packageInformation) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " consumptionAttendanceDay = consumptionAttendanceDay + " + packageInformation.get("formalClass") + "+" + packageInformation.get("giftClass") + ",\n" +
                " positiveConsumptionAttendanceDay = positiveConsumptionAttendanceDay + " + packageInformation.get("formalClass") + ",\n" +
                " giftConsumptionAttendanceDay = giftConsumptionAttendanceDay + " + packageInformation.get("giftClass") + ",\n" +
                " unusedAttendanceDay = unusedAttendanceDay - " + packageInformation.get("formalClass") + "-" + packageInformation.get("giftClass") + ",\n" +
                " positiveUnusedAttendanceDay = positiveUnusedAttendanceDay - " + packageInformation.get("formalClass") + ",\n" +
                " giftUnusedAttendanceDay = giftUnusedAttendanceDay - " + packageInformation.get("giftClass") + ",\n" +
                " absentDay = absentDay + " + packageInformation.get("formalClass") + "+" + packageInformation.get("giftClass") + ", \n" +
                " updateTime = now()" +
                "WHERE\n" +
                "\ttss_member_package.id = '" + packageInformation.get("memberPackageId") + "'";
        System.out.println("sql:" + sql);
        return this.updateBySql(sql);
    }

    @Override
    public int absenteeismDeductiveHours(Map<String, Object> packageInformation) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " consumptionClassHour = consumptionClassHour - " + packageInformation.get("formalClass") + "-" + packageInformation.get("giftClass") + ", \n" +
                " positiveConsumptionClassHour = positiveConsumptionClassHour - " + packageInformation.get("formalClass") + ",\n" +
                " giftConsumptionClassHour = giftConsumptionClassHour - " + packageInformation.get("giftClass") + ",\n" +
                " unusedClassHour = unusedClassHour + " + packageInformation.get("formalClass") + "+" + packageInformation.get("giftClass") + ",\n" +
                " positiveUnusedClassHour = positiveUnusedClassHour + " + packageInformation.get("formalClass") + ",\n" +
                " giftUnusedClassHour = giftUnusedClassHour + " + packageInformation.get("giftClass") + ",\n" +
                " updateTime = now()" +
                "WHERE\n" +
                "\ttss_member_package.id = '" + packageInformation.get("memberPackageId") + "'";
        System.out.println("sql:" + sql);
        return this.updateBySql(sql);
    }

    @Override
    public int absenteeismDeductiveAttendanceDay(Map<String, Object> packageInformation) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " consumptionAttendanceDay = consumptionAttendanceDay - " + packageInformation.get("formalClass") + "-" + packageInformation.get("giftClass") + ",\n" +
                " positiveConsumptionAttendanceDay = positiveConsumptionAttendanceDay - " + packageInformation.get("formalClass") + ",\n" +
                " giftConsumptionAttendanceDay = giftConsumptionAttendanceDay - " + packageInformation.get("giftClass") + ",\n" +
                " unusedAttendanceDay = unusedAttendanceDay + " + packageInformation.get("formalClass") + "+" + packageInformation.get("giftClass") + ",\n" +
                " positiveUnusedAttendanceDay = positiveUnusedAttendanceDay + " + packageInformation.get("formalClass") + ",\n" +
                " giftUnusedAttendanceDay = giftUnusedAttendanceDay + " + packageInformation.get("giftClass") + ",\n" +
                " updateTime = now()" +
                "WHERE\n" +
                "\ttss_member_package.id = '" + packageInformation.get("memberPackageId") + "'";
        System.out.println("sql:" + sql);
        return this.updateBySql(sql);
    }

    @Override
    public int makeUpClassDeductiveHours(Map<String, Object> packageInformation) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " consumptionClassHour = consumptionClassHour + " + packageInformation.get("formalClass") + "+" + packageInformation.get("giftClass") + ", \n" +
                " remediationClassHour = remediationClassHour + " + packageInformation.get("formalClass") + "+" + packageInformation.get("giftClass") + ", \n" +
                " positiveConsumptionClassHour = positiveConsumptionClassHour + " + packageInformation.get("formalClass") + ",\n" +
                " giftConsumptionClassHour = giftConsumptionClassHour + " + packageInformation.get("giftClass") + ",\n" +
                " unusedClassHour = unusedClassHour - " + packageInformation.get("formalClass") + "-" + packageInformation.get("giftClass") + ",\n" +
                " positiveUnusedClassHour = positiveUnusedClassHour - " + packageInformation.get("formalClass") + ",\n" +
                " giftUnusedClassHour = giftUnusedClassHour - " + packageInformation.get("giftClass") + ",\n" +
                " updateTime = now()" +
                "WHERE\n" +
                "\ttss_member_package.id = '" + packageInformation.get("memberPackageId") + "'";
        System.out.println("sql:" + sql);
        return this.updateBySql(sql);
    }

    @Override
    public boolean batchDeductiveHours(ArrayList<Object[]> params) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " consumptionClassHour = consumptionClassHour + ? + ?, \n" +
                " positiveConsumptionClassHour = positiveConsumptionClassHour + ?,\n" +
                " giftConsumptionClassHour = giftConsumptionClassHour + ?,\n" +
                " unusedClassHour = unusedClassHour - ? - ?,\n" +
                " positiveUnusedClassHour = positiveUnusedClassHour - ?,\n" +
                " giftUnusedClassHour = giftUnusedClassHour - ?,\n" +
                " updateTime = now()" +
                "WHERE\n" +
                "\ttss_member_package.id = ?";
        return this.batchUpdate(sql, params);
    }

    @Override
    public boolean batcheductiveAttendanceDay(ArrayList<Object[]> params) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " consumptionAttendanceDay = consumptionAttendanceDay + ? + ?,\n" +
                " positiveConsumptionAttendanceDay = positiveConsumptionAttendanceDay + ?,\n" +
                " giftConsumptionAttendanceDay = giftConsumptionAttendanceDay + ?,\n" +
                " unusedAttendanceDay = unusedAttendanceDay - ? - ?,\n" +
                " positiveUnusedAttendanceDay = positiveUnusedAttendanceDay - ?,\n" +
                " giftUnusedAttendanceDay = giftUnusedAttendanceDay - ?,\n" +
                " updateTime = now()" +
                "WHERE\n" +
                "\ttss_member_package.id = ?";
        return this.batchUpdate(sql, params);
    }

    @Override
    public int reductionMakeUpClassDeductiveHours(Map<String, Object> packageInformation) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " remediationClassHour = remediationClassHour - " + packageInformation.get("formalClass") + "-" + packageInformation.get("giftClass") + " ,\n" +
                " updateTime = now()" +
                "WHERE\n" +
                "\ttss_member_package.id = '" + packageInformation.get("memberPackageId") + "'";
        System.out.println("sql:" + sql);
        return this.updateBySql(sql);
    }

    @Override
    public int reductionAbsenteeismDeductiveHours(Map<String, Object> packageInformation) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " consumptionClassHour = consumptionClassHour - " + packageInformation.get("formalClass") + "-" + packageInformation.get("giftClass") + ", \n" +
                " remediationClassHour = remediationClassHour - " + packageInformation.get("formalClass") + "-" + packageInformation.get("giftClass") + ", \n" +
                " positiveConsumptionClassHour = positiveConsumptionClassHour - " + packageInformation.get("formalClass") + ",\n" +
                " giftConsumptionClassHour = giftConsumptionClassHour - " + packageInformation.get("giftClass") + ",\n" +
                " unusedClassHour = unusedClassHour + " + packageInformation.get("formalClass") + "+" + packageInformation.get("giftClass") + ",\n" +
                " positiveUnusedClassHour = positiveUnusedClassHour + " + packageInformation.get("formalClass") + ",\n" +
                " giftUnusedClassHour = giftUnusedClassHour + " + packageInformation.get("giftClass") + ",\n" +
                " updateTime = now()" +
                "WHERE\n" +
                "\ttss_member_package.id = '" + packageInformation.get("memberPackageId") + "'";
        System.out.println("sql:" + sql);
        return this.updateBySql(sql);
    }

    @Override
    public int addMakeUpClassDeductiveHours(Map<String, Object> packageInformation) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " remediationClassHour = remediationClassHour + " + packageInformation.get("formalClass") + "+" + packageInformation.get("giftClass") + " ,\n" +
                " updateTime = now()" +
                "WHERE\n" +
                "\ttss_member_package.id = '" + packageInformation.get("memberPackageId") + "'";
        System.out.println("sql:" + sql);
        return this.updateBySql(sql);
    }

    @Override
    public Map<String, Object> findApplyStatus(Integer type, String memberId) {
        String sql = "select count(id)as size from tss_member_package where memberId = ? and type = ?\n";

        return this.findOneForJdbc(sql, new Object[]{memberId, type});
    }

    @Override
    public List<TssMemberPackage> findByMemberId(String targetDataId) throws Exception {
        String sql = "select * from tss_member_package where memberId = ?";
        return this.findListBySql(sql, TssMemberPackage.class, targetDataId);
    }

    @Override
    public int attendanceMaintenanceForParentingPackage(Map<String, Object> attendanceMaintenancePackageMap) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " selectedClassHour = selectedClassHour - " + attendanceMaintenancePackageMap.get("formalClass") + " -" + attendanceMaintenancePackageMap.get("giftClass") + ",\n" +
                " consumptionClassHour = consumptionClassHour - " + attendanceMaintenancePackageMap.get("formalClass") + " -" + attendanceMaintenancePackageMap.get("giftClass") + ",\n" +
                " positiveConsumptionClassHour = positiveConsumptionClassHour - " + attendanceMaintenancePackageMap.get("formalClass") + ",\n" +
                " giftConsumptionClassHour = giftConsumptionClassHour - " + attendanceMaintenancePackageMap.get("giftClass") + ",\n" +
                " surplusClassHour = surplusClassHour + " + attendanceMaintenancePackageMap.get("formalClass") + " +" + attendanceMaintenancePackageMap.get("giftClass") + ",\n" +
                " surplusFormalClassHour = surplusFormalClassHour + " + attendanceMaintenancePackageMap.get("formalClass") + ",\n" +
                " surplusPresentClassHour = surplusPresentClassHour + " + attendanceMaintenancePackageMap.get("giftClass") + ",\n" +
                " updateTime = now()" +
                "WHERE\n" +
                "tss_member_package.id = '" + attendanceMaintenancePackageMap.get("memberPackageId") + "'";
        System.out.println("sql:" + sql);
        return this.updateBySql(sql);
    }

    @Override
    public int attendanceMaintenanceForTransitionPackage(Map<String, Object> attendanceMaintenancePackageMap) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " selectedAttendanceDay = selectedAttendanceDay - " + attendanceMaintenancePackageMap.get("formalClass") + " -" + attendanceMaintenancePackageMap.get("giftClass") + ",\n" +
                " consumptionAttendanceDay = consumptionAttendanceDay - " + attendanceMaintenancePackageMap.get("formalClass") + " -" + attendanceMaintenancePackageMap.get("giftClass") + ",\n" +
                " positiveConsumptionAttendanceDay = positiveConsumptionAttendanceDay - " + attendanceMaintenancePackageMap.get("formalClass") + ",\n" +
                " giftConsumptionAttendanceDay = giftConsumptionAttendanceDay - " + attendanceMaintenancePackageMap.get("giftClass") + ",\n" +
                " surplusAttendanceDay = surplusAttendanceDay + " + attendanceMaintenancePackageMap.get("formalClass") + " +" + attendanceMaintenancePackageMap.get("giftClass") + ",\n" +
                " surplusFormalAttendanceDay = surplusFormalAttendanceDay + " + attendanceMaintenancePackageMap.get("formalClass") + ",\n" +
                " surplusPresentAttendanceDay = surplusPresentAttendanceDay + " + attendanceMaintenancePackageMap.get("giftClass") + ",\n" +
                " updateTime = now()" +
                "WHERE\n" +
                "tss_member_package.id = '" + attendanceMaintenancePackageMap.get("memberPackageId") + "'";
        System.out.println("sql:" + sql);
        return this.updateBySql(sql);
    }


    /**
     * 根据合同编号查询课时包数据
     * 杨然
     */
    @Override
    public List<TssMemberPackage> findCountByContract(String contract) {
        String sql = "select a.id, a.memberId, a.contractNumber, a.type from tss_member_package a where a.contractNumber = '" + contract + "'";
        try {
            List<TssMemberPackage> listBySql = super.findListBySql(sql, TssMemberPackage.class, null);
            if (!listBySql.isEmpty())
                return listBySql;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 根据会员id获取条数
     * 杨然
     */
    @Override
    public Integer getCount(String stuId, Integer type) {
        String sql = "select * from tss_member_package a where a.memberId = '" + stuId + "' and type = " + type;
        try {
            List<TssMemberPackage> listBySql = findListBySql(sql, TssMemberPackage.class, null);
            if (!listBySql.isEmpty()) {
                return listBySql.size();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return 0;
    }

    @Override
    public List<TssMemberPackage> findHistory(String paramId) throws Exception {
        String sql = "SELECT\n" +
                "\ttss_member_package.*\n" +
                "FROM\n" +
                "\ttss_member_check_attendance\n" +
                "LEFT JOIN tss_member_attend_class_record ON tss_member_check_attendance.attendClassId = tss_member_attend_class_record.id\n" +
                "LEFT JOIN tss_member_package ON tss_member_attend_class_record.memberPackageId = tss_member_package.id\n" +
                "WHERE tss_member_check_attendance.id in (" + paramId + ")";
        return this.findListBySql(sql, TssMemberPackage.class, null);
    }

    @Override
    public List<Map<String, Object>> finByHistory(String startTime_begin, String type) {
        String sql = "SELECT\n" +
                "\ttss_student.id,\n" +
                "\tSUM(A.consumptionClassHour) AS consumptionClassHours,\n" +
                "\tSUM(A.consumptionAttendanceDay) AS consumptionAttendanceDays\n" +
                "FROM\n" +
                "\ttss_student\n" +
                "LEFT JOIN \n" +
                "(\n" +
                "\tSELECT\n" +
                "\t\tF.*\n" +
                "\tFROM\n" +
                "\t\ttss_member_package\n" +
                "\tLEFT JOIN(\t\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\ttss_member_package_history\n" +
                "\t\tWHERE\n" +
                "\t\t\t1=1\n" +
                "\t\tAND unix_timestamp(\n" +
                "\t\t\ttss_member_package_history.updateTime\n" +
                "\t\t) >= unix_timestamp('" + startTime_begin + "')\n" +
                "\t\tGROUP BY tss_member_package_history.memberPackageId\n" +
                "\t\tORDER BY\n" +
                "\t\ttss_member_package_history.updateTime ASC\n" +
                "\t)F\n" +
                "\tON F.memberPackageId = tss_member_package.id\n" +
                "\tWHERE\n" +
                "\t\ttss_member_package.type = " + type + "\n" +
                ")A \n" +
                "ON tss_student.id = A.memberId\n" +
                "WHERE isMember = 1\n" +
                "GROUP BY tss_student.id";
        System.out.println(sql);
        return findForJdbc(sql, null);
    }

    @Override
    public void absenceRecord(Map<String, Object> packageInformation) {
        String sql = "UPDATE tss_member_package\n" +
                "SET\n" +
                " absentDay = absentDay + " + packageInformation.get("formalClass") + "+" + packageInformation.get("giftClass") + ", \n" +
                " updateTime = now()" +
                "WHERE\n" +
                "\ttss_member_package.id = '" + packageInformation.get("memberPackageId") + "'";
        System.out.println("sql:" + sql);
        this.updateBySql(sql);
    }


}
